USE [SONALIKA_BGK_UPDATED]
GO

/****** Object:  StoredProcedure [dbo].[SpSpareStock]    Script Date: 05/10/2014 15:06:30 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SpSpareStock]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SpSpareStock]
GO

USE [SONALIKA_BGK_UPDATED]
GO

/****** Object:  StoredProcedure [dbo].[SpSpareStock]    Script Date: 05/10/2014 15:06:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[SpSpareStock]--1,4

AS 
BEGIN
IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL DROP TABLE #TEMP

CREATE TABLE #TEMP(SPARE_ID INT,P_QNTY INT,S_QNTY INT,UNIT_COST DECIMAL(18,2))

INSERT INTO #TEMP

SELECT SPARE_PART_ID,SUM(QUANTITY)AS QUANTITY,0,SPARE_UNIT_COST FROM dbo.SPARE_PURCHASES_SALES
where  TRANSACTION_TYPE=(SELECT MASTER_ID FROM MASTER WHERE MASTER_VALUE='PURCHASE')
GROUP BY SPARE_UNIT_COST,SPARE_PART_ID
UNION

SELECT SPARE_PART_ID,0,SUM(QUANTITY)AS QUANTITY,SPARE_UNIT_COST FROM dbo.SPARE_PURCHASES_SALES
where  TRANSACTION_TYPE=(SELECT MASTER_ID FROM MASTER WHERE MASTER_VALUE='SALE')
GROUP BY SPARE_UNIT_COST,SPARE_PART_ID

SELECT T.SPARE_ID,SUM(P_QNTY-S_QNTY)AS STOCK,UNIT_COST ,SPARE_PART_CODE,SPARE_PART_DESCRIPTION FROM #TEMP T
--LEFT OUTER JOIN SPARE_PURCHASES_SALES SPS ON SPS.SPARE_PART_ID=T.SPARE_ID
--LEFT OUTER JOIN SUPPLIER S ON S.SUPPLIER_ID=SPS.SUPPLIER_ID
LEFT OUTER JOIN dbo.SPARE_PARTS SP ON SP.SPARE_PART_ID=T.SPARE_ID
GROUP BY UNIT_COST,T.SPARE_ID,SPARE_PART_CODE,SPARE_PART_DESCRIPTION
ORDER BY SPARE_PART_CODE DESC
--SELECT SPARE_PART_ID,SPARE_PART_CODE,SPARE_PART_DESCRIPTION FROM dbo.SPARE_PARTS
--WHERE SPARE_PART_ID IN(SELECT SPARE_ID FROM #TEMP)

END


GO


